﻿Imports Microsoft.Office.Interop
Imports System.Windows.Forms
Imports System.Drawing


Public Class Dgv_Direct_to_Excel
    Private CutCellValue As Boolean

    Private Const _LibVersion As String = "Dgv_Direct_to_Excel : 0.0.0.1"
    ReadOnly Property LibVersion() As String
        Get
            Return _LibVersion
        End Get
    End Property


    Private _Export_OK As Boolean = False
    ReadOnly Property Export_OK() As Boolean
        Get
            Return _Export_OK
        End Get
    End Property

    Private _WorkSheetName As String
    ReadOnly Property WorkSheetName() As String
        Get
            Return _WorkSheetName
        End Get
    End Property

    ''' <summary>
    ''' Input with DataGridView
    ''' </summary>
    ''' <param name="dgv"></param>
    ''' <remarks></remarks>
    Sub New(dgv As DataGridView)
        Call Export(dgv, String.Empty, False)
    End Sub

    ''' <summary>
    ''' Input with DataGridView and WorkSheetName
    ''' </summary>
    ''' <param name="dgv"></param>
    ''' <param name="WorkSheetName"></param>
    ''' <remarks></remarks>
    Sub New(dgv As DataGridView, WorkSheetName As String)
        Call Export(dgv, WorkSheetName, False)
    End Sub

    ''' <summary>
    ''' Input with DataGridView and WorkSheetName and CellFormating
    ''' </summary>
    ''' <param name="dgv"></param>
    ''' <param name="WorkSheetName"></param>
    ''' <param name="withCellFormats"></param>
    ''' <remarks></remarks>
    Sub New(dgv As DataGridView, WorkSheetName As String, withCellFormats As Boolean)
        Call Export(dgv, WorkSheetName, withCellFormats)
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="dgv"></param>
    ''' <param name="WorkSheetName"></param>
    ''' <remarks></remarks>
    Private Sub Export(dgv As DataGridView, WorkSheetName As String, withCellFormats As Boolean)
        'Dim oldCursor As Cursor
        'oldCursor = Me.Cursor
        Try
            '  oldCursor = Cursors.WaitCursor
            start_and_export_Excel(dgv, WorkSheetName, withCellFormats)
            ' oldCursor = Cursors.[Default]
            If _Export_OK Then
                If is_Language_German_Active() Then
                    MessageBox.Show("Die Arbeitsmappe ist gefüllt!", "Export nach Excel", MessageBoxButtons.OK)
                Else
                    MessageBox.Show("Excel-Sheet is filled!", "Export to Excel", MessageBoxButtons.OK)
                End If
            End If
            Exit Sub
        Catch ex As Exception
            MessageBox.Show(ex.Message(), "Excel-Export - Exception")
        Finally
            'oldCursor = Cursors.[Default]
        End Try
    End Sub

    ''' <summary>
    ''' Start ExcelApplicaton 
    ''' </summary>
    ''' <param name="dgv"></param>
    ''' <param name="WorkSheetName"></param>
    ''' <param name="withCellFormats"></param>
    ''' <remarks></remarks>
    Private Sub start_and_export_Excel(dgv As DataGridView, WorkSheetName As String, withCellFormats As Boolean)

        Dim oXLs As Excel.Application
        Dim oWB As Excel.Workbook

        ' Start Excel and get Application object.
        Try
            dgv.EndEdit()
            dgv.Update()


            oXLs = CType(CreateObject("Excel.Application"), Excel.Application)
            Try

                With oXLs.Application
                    .DecimalSeparator = "."
                    .ThousandsSeparator = ","
                    .UseSystemSeparators = False
                End With



                oXLs.Visible = True
                oWB = oXLs.Workbooks.Add

                Dim WrapText As Boolean
                _Export_OK = Export_DataTable_Content_To_Excel(oWB, dgv, WorkSheetName, withCellFormats, WrapText)


            Catch ex As Exception
                MessageBox.Show(ex.Message(), "Excel-Export-Working - Exception")
                Exit Sub
            End Try

            Exit Sub
        Catch ex As Exception
            _Export_OK = Alternativ_CSV_Export(dgv, True)
            Exit Sub
        End Try
    End Sub




    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="dgv"></param>
    ''' <param name="Start_with_Application"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function Alternativ_CSV_Export(dgv As DataGridView, Start_with_Application As Boolean) As Boolean
        Try
            Dim saveFileDialog1 As New SaveFileDialog()
            saveFileDialog1.Filter = "csv-File|*.csv"
            saveFileDialog1.Title = "Save as Csv-File"
            saveFileDialog1.AddExtension = True
            saveFileDialog1.DefaultExt = "csv"
            saveFileDialog1.FileName = "Export.csv"

            If (saveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
                Dim csvFile_Export As New Export_DGV_to_csv(dgv, saveFileDialog1.FileName)
                Return csvFile_Export.Export_OK
            End If
            Return False
        Catch ex As Exception
            Return False
        End Try
    End Function


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="oWB"></param>
    ''' <param name="dgv"></param>
    ''' <param name="WrapText"></param>
    ''' <param name=" WorkSheetName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function Export_DataTable_Content_To_Excel(oWB As Excel.Workbook, dgv As DataGridView, WorkSheetName As String, withCellFormats As Boolean, WrapText As Boolean) As Boolean
        Dim iExcelRow As Integer

        Try

            If dgv.Columns.Count <= 0 Then
                If is_Language_German_Active() Then
                    MessageBox.Show("Fehler: DataGridView hat keine Spalten!", "Export nach Excel", MessageBoxButtons.OK)
                Else
                    MessageBox.Show("Error: DataGridview has no Colums!", "Export to Excel", MessageBoxButtons.OK)
                End If
                Return False
            End If


            Dim oSheet As Excel.Worksheet
            oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)


            'make Excel Columns Header 
            iExcelRow = 1
            For nColumn As Integer = 0 To dgv.Columns.Count - 1
                'fill Cell Text with Values 
                DirectCast(oSheet.Cells(iExcelRow, nColumn + 1), Excel.Range).Value = Cut_Excel_CELL_Value(dgv.Columns(nColumn).HeaderText)
            Next



            'storing dataGridview Rows to Excel
            For Each dgv_row As DataGridViewRow In dgv.Rows()
                If Not dgv_row.IsNewRow Then
                    iExcelRow = iExcelRow + 1
                    Dim Count_Of_Lines = dgv.Columns.Count() - 1

                    For nColumn As Integer = 0 To Count_Of_Lines
                        If dgv_row.Cells.Item(nColumn).Value IsNot Nothing Then

                            'fill Cell Text with Values 
                            DirectCast(oSheet.Cells(iExcelRow, nColumn + 1), Excel.Range).Value = Cut_Excel_CELL_Value(dgv_row.Cells.Item(nColumn).Value.ToString())


                            If WrapText Then
                                DirectCast(oSheet.Cells(iExcelRow, nColumn + 1), Excel.Range).WrapText = True
                            Else
                                DirectCast(oSheet.Cells(iExcelRow, nColumn + 1), Excel.Range).WrapText = False
                            End If
                        End If
                    Next
                End If
            Next

            Call Set_Excel_SheetName(oSheet, dgv.Name)

            oSheet.UsedRange.Columns.AutoFit()
            oSheet.UsedRange.Rows.AutoFit()

            If WorkSheetName.Length > 0 Then
                oSheet.Name = WorkSheetName
            End If
            _WorkSheetName = oSheet.Name

            Call set_Excel_AutoFilter(oSheet)

            Return True

        Catch ex As Exception
            MessageBox.Show(ex.Message(), "Info - Exception")
            Return False
        End Try
    End Function



    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="oWS"></param>
    ''' <remarks></remarks>
    Private Sub set_Excel_AutoFilter(ByRef oWS As Excel.Worksheet)

        Try
            Dim Selection As Excel.Range = oWS.UsedRange()
            Selection.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, True)

            Selection = CType(oWS.Cells(1, 1), Excel.Range)
            Selection.Activate()

            Exit Sub
        Catch ex As Exception
            MessageBox.Show(ex.Message(), "Info - Exception, create Auto-Filter", MessageBoxButtons.OK)
        End Try
    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="oWS"></param>
    ''' <param name="TableRangeName"></param>
    ''' <remarks></remarks>
    Private Sub set_Excel_AutoFilter_ByRange(ByRef oWS As Excel.Worksheet, TableRangeName As String)
        Try

            'set TableRangeName
            If TableRangeName IsNot Nothing Then
                TableRangeName = TableRangeName.Trim
                If TableRangeName.Length > 0 Then
                    TableRangeName = "My_DataGridView_List"
                End If
            End If

            Dim FilterList As [String]() = {}
            oWS.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, oWS.UsedRange, System.Type.Missing, Excel.XlYesNoGuess.xlYes).Name = TableRangeName
            Exit Sub
        Catch ex As Exception
            MessageBox.Show(ex.Message(), "Info - Exception, create Named-Auto-Filter", MessageBoxButtons.OK)
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="oWS"></param>
    ''' <param name="SheetName"></param>
    ''' <remarks></remarks>
    Private Sub Set_Excel_SheetName(ByRef oWS As Excel.Worksheet, SheetName As String)
        Try

            If SheetName.Length > 0 Then
                oWS.Name = SheetName
            Else
                oWS.Name = "Table"
            End If

            Exit Sub
        Catch ex As Exception
            MessageBox.Show(ex.Message(), "Info - Exception, Set_Excel_SheetName")
        End Try
    End Sub


    Private Function Cut_Excel_CELL_Value(ExcelCell_Text As String) As String
        Const max_Length_a_ExcelCell_Length = 256

        If ExcelCell_Text = String.Empty Then
            Cut_Excel_CELL_Value = String.Empty
            Exit Function
        End If

        If ExcelCell_Text.Length() > max_Length_a_ExcelCell_Length Then
            Cut_Excel_CELL_Value = "~" & Left(ExcelCell_Text, max_Length_a_ExcelCell_Length - 1)
            CutCellValue = True
        Else
            Cut_Excel_CELL_Value = ExcelCell_Text
        End If

    End Function

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function is_Language_German_Active() As Boolean
        Try
            Dim cultureName As String = Threading.Thread.CurrentThread.CurrentCulture.Name
            If cultureName.Substring(0, 2) = "de" Then
                is_Language_German_Active = True
            Else
                is_Language_German_Active = False
            End If
        Catch
            is_Language_German_Active = False
        End Try
    End Function


End Class
